package com.eps.service;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;

import jxl.Workbook;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.springframework.stereotype.Service;

@Service
public class ExcelManager implements IExcelManager{

	
	public void exportar(ArrayList datosTabla, String nombreTab,
			String titulo, OutputStream out) {
		// TODO Auto-generated method stub
		int MARGEN_IZQUIERDO_TEMP=3;
        int MARGEN_TOP_TEMP=3;
        ArrayList identificadores = (ArrayList) datosTabla.remove(datosTabla.size() - 1);
        ArrayList clases = (ArrayList) datosTabla.remove(datosTabla.size() - 1);
        try
        {

        WritableWorkbook w = Workbook.createWorkbook(out);

        WritableSheet s = w.createSheet(nombreTab, 0);
        s.getSettings().setProtected(true);

        /* Format the Font */
            WritableFont wf = new WritableFont(WritableFont.ARIAL,
              14, WritableFont.BOLD);
            wf.setColour(jxl.format.Colour.BLACK);
            WritableCellFormat cf = new WritableCellFormat(wf);
            //cf.setWrap(true);
            cf.setShrinkToFit(true);
            cf.setAlignment(jxl.format.Alignment.CENTRE);
            s.mergeCells(0+MARGEN_IZQUIERDO_TEMP, 0+MARGEN_TOP_TEMP, identificadores.size()-1+MARGEN_IZQUIERDO_TEMP, 0+MARGEN_TOP_TEMP);
            s.addCell(new Label(0+MARGEN_IZQUIERDO_TEMP, 0+MARGEN_TOP_TEMP, titulo,cf));


         wf = new WritableFont(WritableFont.ARIAL,
              12, WritableFont.BOLD);
            wf.setColour(jxl.format.Colour.BLACK);
            cf = new WritableCellFormat(wf);
            cf.setAlignment(jxl.format.Alignment.CENTRE);
            cf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.HAIR,jxl.format.Colour.GRAY_80);
           // cf.setShrinkToFit(true);
            cf.setWrap(true);
            cf.setBackground(jxl.format.Colour.GRAY_25);

            //titulos tabla
            for(int j=0;j<identificadores.size();j++){
                s.addCell(new Label(j+MARGEN_IZQUIERDO_TEMP, 1+MARGEN_TOP_TEMP, identificadores.get(j).toString(),cf));
            }

            wf = new WritableFont(WritableFont.TAHOMA,
              10, WritableFont.NO_BOLD);
            wf.setColour(jxl.format.Colour.BLACK);
            cf = new WritableCellFormat(wf);
            cf.setShrinkToFit(true);
            cf.setLocked(false);
            //cf.setWrap(true);

            String[] let = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O"};
            //cuerpo tabla
            for(int i=0;i< datosTabla.size();i++){
                    ArrayList datos_columna = (ArrayList)datosTabla.get(i);
                    for(int j=0;j<datos_columna.size();j++){
                            Object objeto=datos_columna.get(j);
                             if(objeto instanceof Double){

                                jxl.write.Number n = new jxl.write.Number(j+MARGEN_IZQUIERDO_TEMP,i+2+MARGEN_TOP_TEMP,Double.parseDouble(objeto.toString()),cf);
                                 s.addCell(n);
                            }
                            else if(objeto instanceof BigDecimal){
                                jxl.write.Number n = new jxl.write.Number(j+MARGEN_IZQUIERDO_TEMP,i+2+MARGEN_TOP_TEMP,new Double(((BigDecimal)objeto).doubleValue()),cf);
                                 s.addCell(n);
                            }
                            else{

                                s.addCell(new Label(j+MARGEN_IZQUIERDO_TEMP, i+MARGEN_TOP_TEMP+2, String.valueOf(objeto),cf));
                            }
                    }
            }
            for(int j=0;j<identificadores.size();j++){
               // s.addCell(new Label(j+MARGEN_IZQUIERDO_TEMP, 1+MARGEN_TOP_TEMP, identificadores.get(j).toString(),cf));
                s.addCell(new Formula(j+MARGEN_IZQUIERDO_TEMP, datosTabla.size()-1+MARGEN_TOP_TEMP+3, "SUMA("+let[j+MARGEN_IZQUIERDO_TEMP]+(3+MARGEN_TOP_TEMP)+":"+let[j+MARGEN_IZQUIERDO_TEMP]+(3+MARGEN_TOP_TEMP+datosTabla.size()-1)+")"));
            }
            
            w.write();
            w.close();
            out.close();

            

        }catch(IOException ex){ex.printStackTrace();}
        catch(WriteException ex){ex.printStackTrace();}
       // return out;
	}




}
